package com.bcu.cn.onlineexamsystem.exammanage.mapper;

import com.bcu.cn.onlineexamsystem.exammanage.entity.Exam;
import com.bcu.cn.onlineexamsystem.exammanage.entity.Question;
import com.bcu.cn.onlineexamsystem.exammanage.entity.StudentExam;
import org.apache.ibatis.annotations.*;
import java.util.List;
import java.util.Map;

@Mapper
public interface ExamMapper {
    
    @Select("SELECT e.*, c.course_name, u.real_name as teacher_name " +
            "FROM exams e " +
            "LEFT JOIN courses c ON e.course_id = c.course_id " +
            "LEFT JOIN users u ON e.teacher_id = u.user_id " +
            "WHERE e.teacher_id = #{teacherId} " +
            "ORDER BY e.start_time DESC")
    List<Exam> findByTeacherId(String teacherId);
    
    // 添加一个测试查询，用于验证数据库连接
    @Select("SELECT COUNT(*) FROM exams")
    int countExams();
    
    @Insert("INSERT INTO exams (course_id, exam_name, exam_type, teacher_id, start_time, end_time, duration, total_score, status) " +
            "VALUES (#{courseId}, #{examName}, #{examType}, #{teacherId}, #{startTime}, #{endTime}, #{duration}, #{totalScore}, 0)")
    @Options(useGeneratedKeys = true, keyProperty = "examId")
    int insertExam(Exam exam);
    
    @Insert("INSERT INTO exam_questions (exam_id, question_id, order_num) VALUES (#{examId}, #{questionId}, #{orderNum})")
    int insertExamQuestion(@Param("examId") Long examId, @Param("questionId") Long questionId, @Param("orderNum") Integer orderNum);
    
    @Insert("INSERT INTO exam_classes (exam_id, class_id) VALUES (#{examId}, #{classId})")
    int insertExamClass(@Param("examId") Long examId, @Param("classId") Long classId);

    @Delete("DELETE FROM exams WHERE exam_id = #{examId}")
    int deleteExam(@Param("examId") String examId);

    @Update("UPDATE exams SET status = #{status} WHERE exam_id = #{examId}")
    int updateExamStatus(@Param("examId") String examId, @Param("status") Integer status);

    @Select("SELECT e.*, c.course_name, u.real_name as teacher_name " +
            "FROM exams e " +
            "LEFT JOIN courses c ON e.course_id = c.course_id " +
            "LEFT JOIN users u ON e.teacher_id = u.user_id " +
            "WHERE e.exam_id = #{examId}")
    Exam getExamDetail(@Param("examId") String examId);

    @Select("SELECT q.*, eq.order_num " +
            "FROM exam_questions eq " +
            "LEFT JOIN questions q ON eq.question_id = q.question_id " +
            "WHERE eq.exam_id = #{examId} " +
            "ORDER BY eq.order_num")
    List<Question> getExamQuestions(@Param("examId") String examId);

    @Select("SELECT se.*, u.real_name as student_name, c.class_name " +
            "FROM student_exams se " +
            "LEFT JOIN users u ON se.student_id = u.user_id " +
            "LEFT JOIN classes c ON u.class_id = c.class_id " +
            "WHERE se.exam_id = #{examId}")
    List<StudentExam> getExamRecords(@Param("examId") String examId);

    /**
     * 查询符合考试条件的学生列表
     * @param examId 考试ID
     * @param courseId 课程ID
     * @return 学生ID列表
     */
    @Select("SELECT u.user_id FROM users u " +
            "INNER JOIN student_courses sc ON u.user_id = sc.student_id " +
            "WHERE u.class_id IN " +
            "(SELECT class_id FROM exam_classes WHERE exam_id = #{examId}) " +
            "AND sc.course_id = #{courseId} " +
            "AND u.role = 'student'")
    List<String> findEligibleStudents(@Param("examId") Long examId, @Param("courseId") Long courseId);

    /**
     * 为学生创建考试记录
     * @param studentId 学生ID
     * @param examId 考试ID
     */
    @Insert("INSERT INTO student_exams (student_id, exam_id, status) VALUES (#{studentId}, #{examId}, 0)")
    void insertStudentExam(@Param("studentId") String studentId, @Param("examId") Long examId);

    // 查询所有进行中且未结束的考试
    @Select("SELECT * FROM exams WHERE status = 1 AND end_time < NOW()")
    List<Exam> findOngoingExamsShouldEnd();

    // 批量将考试状态设置为已结束
    @Update("UPDATE exams SET status = 2 WHERE status = 1 AND end_time < NOW()")
    int endAllExpiredExams();

    /**
     * 查询考试各题答题统计（仅限本场考试下的答题情况）
     * optionStats为选择题每个选项被选人数数组（JSON字符串）
     */
    @Select({
        "SELECT q.question_id, q.content, q.type, q.options, q.answer, q.score, ",
        // 正确/错误人数限定在本场考试
        "(SELECT COUNT(*) FROM student_answers sa INNER JOIN student_exams se ON sa.record_id = se.record_id WHERE sa.question_id = q.question_id AND sa.score = q.score AND se.exam_id = #{examId}) AS correctCount, ",
        "(SELECT COUNT(*) FROM student_answers sa INNER JOIN student_exams se ON sa.record_id = se.record_id WHERE sa.question_id = q.question_id AND sa.score < q.score AND se.exam_id = #{examId}) AS wrongCount, ",
        // optionStats: 针对选择题，统计每个选项被选人数（仅本场考试）
        "CASE WHEN q.type = 'choice' THEN (",
        "  SELECT CONCAT('[', GROUP_CONCAT(cnt ORDER BY idx), ']') FROM (",
        "    SELECT idx, COUNT(sa.answer_id) AS cnt FROM (",
        "      SELECT CAST(JSON_UNQUOTE(JSON_EXTRACT(q.options, CONCAT('$[', n.n, ']'))) AS CHAR) AS opt, n.n AS idx",
        "      FROM (SELECT 0 n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5) n",
        "      WHERE n.n < JSON_LENGTH(q.options)",
        "    ) opt_idx",
        "    LEFT JOIN student_answers sa ON sa.question_id = q.question_id AND sa.answer = opt_idx.opt",
        "    INNER JOIN student_exams se ON sa.record_id = se.record_id AND se.exam_id = #{examId}",
        "    GROUP BY idx",
        "    ORDER BY idx",
        "  ) t",
        ") ELSE NULL END AS optionStats ",
        "FROM exam_questions eq LEFT JOIN questions q ON eq.question_id = q.question_id WHERE eq.exam_id = #{examId} ORDER BY eq.order_num"
    })
    List<Map<String, Object>> getExamQuestionStat(@Param("examId") String examId);
} 